Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Using Constraints

Constraints can be implimented at one of the following times:

  Table creation. When the table is created and the columns are defined, the integrity constraints can be specified.
  Table modification. Constraints can be added to already existing tables by using the ALTER TABLE command.

Implementing Constraints at Table Creation

When creating tables, you can add constraints to the column definitions as show in the following CREATE TABLE command:

CREATE TABLE breeds
( breed NUMBER(4) PRIMARY KEY,
  breed_name VARCHAR2(25) NOT NULL,
  description VARCHAR2(40) NOT NULL)
TABLESPACE dog_space;

CREATE TABLE dogs
( dogname VARCHAR2(40) NOT NULL,
  age NUMBER(2) NOT NULL
    CONSTRAINT age_check
      CHECK ( age < 20 ),
  breed NUMBER(4)
    CONSTRAINT breed_cons
    REFERENCES breeds ON DELETE CASCADE,
  owner VARCHAR2(40) NOT NULL )
TABLESPACE dog_space;

When specifying referential integrity constraints, you can specify two additional options:


Option Definition

ON DELETE CASCADE Specifies that if rows in the parent table are deleted, the rows referenced in the child table are also deleted.
No Action By not specifying the ON DELETE CASCADE qualifier, no changes are allowed to parent table columns that have values in child tables that reference them.

Implementing Constraints on Existing Tables

For existing tables, you can add constraints with the ALTER TABLE command, as shown here (all three commands are required to alter the verification methods used by the DOGS and BREEDS tables introduced earlier in this chapter):

ALTER TABLE breeds
    ADD PRIMARY KEY (breed);
ALTER TABLE dogs
    ADD FOREIGN KEY (breed) REFERENCES breeds(breed);

ALTER TABLE dogs
    ADD CHECK (age < 20);

Viewing Constraints

When you add constraints to tables, these definitions are added to the data dictionary. The constraint information is kept internal tables in the data dictionary. These tables can be queried in several views. The following chart lists the views defined for constraint information.


View Which Constraints?

ALL_CONSTRAINTS Constraints on objects accessible by the user.
USER_CONSTRAINTS Constraints on objects owned by the user.
DBA_CONSTRAINTS Constraints on all objects in the system.
ALL_CONS_COLUMNS Column information about constraints on objects accessible by the user.
USER_CONS_COLUMNS Column information about constraints on objects owned by the user.
DBA_CONS_COLUMNS Column information about constraints on all objects in the system.

You can obtain information about which tables have constraints applied to them by querying the data dictionary in this manner:

SQL> SELECT
  2    constraint_name,
   3    constraint_type,
 4    table_name,
  5    r_constraint_name
6  FROM
  7     user_constraints;

CONSTRAINT_NAME            C TABLE_NAME            R_CONSTRAINT_NAME
--------------------       -       ----------------   --------------
SYS_C00388                 C BREEDS
SYS_C00389                 C BREEDS
SYS_C00390                 C BREEDS
SYS_C00391                 P BREEDS
SYS_C00378                 C DOGS
SYS_C00379                 C DOGS
SYS_C00380                 C DOGS
SYS_C00381                 C DOGS
SYS_C00392                 R DOGS                   SYS_C00391
SYS_C00404                 C DOGS

10 rows selected.

Alternatively, you can obtain specific constraint information in this way:

SQL> SELECT
  2    constraint_name,
  3    search_condition
 4  FROM
  5    user_constraints
 6  WHERE
  7    table_name = 'DOGS';

CONSTRAINT_NAME
------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------
SYS_C00378
DOGNAME IS NOT NULL

SYS_C00379
AGE IS NOT NULL

SYS_C00380
BREED IS NOT NULL

SYS_C00381
OWNER IS NOT NULL

SYS_C00392

SYS_C00404
age < 20

6 rows selected.

To get column information, you can use the following statement:

SQL> SELECT
   2    constraint_name,
  3    table_name,
 4    column_name
5  FROM
 6    user_cons_columns;

CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME
--------------------              --------------------          ----------
SYS_C00378                     DOGS                           DOGNAME
SYS_C00379                     DOGS                            AGE
SYS_C00380                     DOGS                           BREED
SYS_C00381                     DOGS                           OWNER
SYS_C00388                     BREEDS                          BREED
SYS_C00389                     BREEDS                         BREED_NAME
SYS_C00390                     BREEDS                         DESCRIPTION
SYS_C00391                     BREEDS                         BREED
SYS_C00392                     DOGS                           BREED
SYS_C00404                     DOGS                           AGE

10 rows selected.
Review of Integrity Constraints

Constraints can be a very powerful tool for protecting the integrity of your data. By using Oracle integrity constraints, you can simplify the application development process and gain additional efficiency by using internal Oracle functions. If constraints and business rules are needed in your application and for your database, it is much more efficient to use the Oracle integrity constraints rather than coding the validation procedures yourself.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.